package cn.com.dragonpass.infra.report.utils;
import cn.com.dragonpass.mongo.MongoFileClient;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

public class ExcelUtil {
    private static final String FORMAT = "yyyy-MM-dd HH:mm:ss";
    private static final ThreadLocal<SimpleDateFormat> sdfThreadLocal = ThreadLocal.withInitial(() -> new SimpleDateFormat(FORMAT));
    /**
     * 根据页码和每页数量分批读取 Excel 文件并处理数据
     *
     * @param fileUrl 文件的 URL
     * @param pageNo 当前页码
     * @param pageSize 每页的数量
     * @return 返回处理后的数据列表
     */
    public static List<List<String>> readExcelBatch(String fileUrl, int pageNo, int pageSize) throws IOException {
        MongoFileClient client = MongoFileClient.createDefaultClient("/");
        byte[] byPath = client.getByPath(fileUrl);
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        baos.write(byPath);
        InputStream is = new ByteArrayInputStream(baos.toByteArray());

        List<List<String>> resultList = new ArrayList<>();
        try {
            // 创建 Workbook 对象
            Workbook workbook = WorkbookFactory.create(is);
            Sheet sheet = workbook.getSheetAt(0); // 假设只处理第一个工作表
            DataFormatter dataFormatter = new DataFormatter();
            // 计算开始读取的行号
            int startRowNum = (pageNo - 1) * pageSize;
            int endRowNum = Math.min(startRowNum + pageSize, sheet.getLastRowNum() + 1);

            // 获取表头行的单元格数目
            int headerRowCount = sheet.getRow(0).getLastCellNum();

            // 读取指定范围内的行
            for (int i = startRowNum; i < endRowNum; i++) {
                Row row = sheet.getRow(i);
                if (row != null) {
                    List<String> rowValues = new ArrayList<>(headerRowCount);

                    // 初始化单元格值列表
                    for (int j = 0; j < headerRowCount; j++) {
                        rowValues.add("");
                    }

                    // 处理单元格
                    Iterator<Cell> cellIterator = row.cellIterator();
                    while (cellIterator.hasNext()) {
                        Cell cell = cellIterator.next();
                        int columnIndex = cell.getColumnIndex();
                        if (columnIndex < headerRowCount) {
                            String cellValue = getCellValue(cell, dataFormatter);
                            rowValues.set(columnIndex, cellValue);
                        }
                    }

                    // 处理合并单元格
//                    for (CellRangeAddress mergedRegion : sheet.getMergedRegions()) {
//                        int firstRow = mergedRegion.getFirstRow();
//                        int lastRow = mergedRegion.getLastRow();
//                        int firstCol = mergedRegion.getFirstColumn();
//                        int lastCol = mergedRegion.getLastColumn();
//                        if (firstRow <= i && i <= lastRow) {
//                            String mergedCellValue = getCellValue(sheet.getRow(firstRow).getCell(firstCol));
//                            for (int j = firstCol; j <= lastCol; j++) {
//                                if (columnIndex < headerRowCount) {
//                                    rowValues.set(j, mergedCellValue);
//                                }
//                            }
//                        }
//                    }

                    resultList.add(rowValues);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        return resultList;
    }


//    /**
//     * 根据页码和每页数量分批读取 Excel 文件并处理数据
//     *
//     * @param fileUrl 文件的 URL
//     * @param pageNo 当前页码
//     * @param pageSize 每页的数量
//     * @return 返回处理后的数据列表
//     */
//    public static List<List<String>> readExcelBatch(String fileUrl, int pageNo, int pageSize) throws IOException {
//        MongoFileClient client = MongoFileClient.createDefaultClient("/");
//        byte[] byPath = client.getByPath(fileUrl);
//        ByteArrayOutputStream baos = new ByteArrayOutputStream();
//        baos.write(byPath);
//        InputStream is = new ByteArrayInputStream(baos.toByteArray());
//
//        List<List<String>> resultList = new ArrayList<>();
//        try {
//            // 创建 Workbook 对象
//            Workbook workbook = WorkbookFactory.create(is);
//            Sheet sheet = workbook.getSheetAt(0); // 假设只处理第一个工作表
//
//            // 计算开始读取的行号
//            int startRowNum = (pageNo - 1) * pageSize;
//            int endRowNum = Math.min(startRowNum + pageSize, sheet.getLastRowNum() + 1);
//
//            // 读取指定范围内的行
//            for (int i = startRowNum; i < endRowNum; i++) {
//                Row row = sheet.getRow(i);
//                if (row != null) {
//                    List<String> rowValues = new ArrayList<>();
//                    Iterator<Cell> cellIterator = row.cellIterator();
//                    while (cellIterator.hasNext()) {
//                        Cell cell = cellIterator.next();
//                        String cellValue = getCellValue(cell);
//                        rowValues.add(cellValue);
//                    }
//                    resultList.add(rowValues);
//                }
//            }
//        } catch (Exception e) {
//            e.printStackTrace();
//        }
//
//        return resultList;
//    }

    /**
     * 解析excel的数据
     * @return
     */
    public static List<List<String>> parseData(InputStream inputStream) throws IOException {
        // 表格数据
        List<List<String>> excelData = new ArrayList<List<String>>();

        XSSFWorkbook workbook = new XSSFWorkbook(inputStream);

        // 读取第一张sheet
        XSSFSheet sheet = workbook.getSheetAt(0);
        DataFormatter dataFormatter = new DataFormatter();
        // 获取总行数
        int rowCount = getRowCountExcludingEmptyRows(sheet);//sheet.getPhysicalNumberOfRows();

        // 总列数
        int cellCount = 0;
        if(rowCount>0){
            // 获取表头总列数
            cellCount = sheet.getRow(0).getPhysicalNumberOfCells();
        }
        for (int r = 0; r < rowCount; r++) {
            if (sheet.getRow(r) == null) {
                continue;
            }else {
                XSSFRow row = sheet.getRow(r);
                List<String> tempList = new ArrayList<String>();
                for (int c = 0; c < cellCount; c++) {
                    //获取第 c 列
                    Cell cell = row.getCell(c);
                    String cellValue = "";
                    if (cell != null) {
                        cellValue = getCellValue(cell,dataFormatter);
                    }else {
                        cellValue = "";
                    }
                    //存储excel中每一行解析后的数据
                    tempList.add(cellValue);
                }
                excelData.add(tempList);
            }
        }
        return excelData;
    }

    private static int getRowCountExcludingEmptyRows(Sheet sheet) {
        int rowCount = 0;
        for (int i = 0; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            if (!isRowEmpty(row)) {
                rowCount++;
            }
        }
        return rowCount;
    }

    private static boolean isRowEmpty(Row row) {
        if (row == null) {
            return true;
        }
        for (int j = 0; j < row.getLastCellNum(); j++) {
            Cell cell = row.getCell(j, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
            if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                return false;
            }
        }
        return true;
    }

    /**
     * @Title: getCellValue
     * @Description: 获取excel单元格数据
     * @param cell
     * @return String
     * @throws
     */
    private static String getCellValue(Cell cell, DataFormatter dataFormatter) {
        /*int cellType = cell.getCellType();
        String cellValue = null;
        switch(cellType) {
            case Cell.CELL_TYPE_STRING: //文本
                cellValue = cell.getStringCellValue();
                break;
            case Cell.CELL_TYPE_NUMERIC: //数字、日期
                cellValue = String.valueOf(cell.getNumericCellValue()); //数字
                break;
            case Cell.CELL_TYPE_BOOLEAN: //布尔型
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_BLANK: //空白
                cellValue = cell.getStringCellValue();
                break;
            case Cell.CELL_TYPE_ERROR: //错误
                cellValue = "";
                break;
            case Cell.CELL_TYPE_FORMULA: //公式
                cellValue = "";
                break;
            default:
                cellValue = "";
        }
        return cellValue;*/

        String cellValue = null;
        CellType cellType = cell.getCellTypeEnum();

        switch (cellType) {
            case STRING:
                // 处理文本数据
                cellValue = cell.getStringCellValue();
                break;
            case NUMERIC:
                //cellValue = String.valueOf(cell.getNumericCellValue());
                if (DateUtil.isCellDateFormatted(cell)) {
                    // 处理日期时间数据
                    Date dateValue = cell.getDateCellValue();
                    cellValue = sdfThreadLocal.get().format(dateValue);
                } else {
                    // 处理数值数据
                    double numericValue = cell.getNumericCellValue();
                    BigDecimal bigDecimalValue = new BigDecimal(numericValue);
                    cellValue = bigDecimalValue.toPlainString();
                }
                break;
            case BOOLEAN:
                // 处理布尔值数据
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;

            case FORMULA:
                // 处理公式数据
                cellValue = dataFormatter.formatCellValue(cell);
                break;

            case BLANK:
                // 处理空白单元格
                cellValue = cell.getStringCellValue();
                break;

            case ERROR:
                // 处理错误单元格
                cellValue = String.valueOf(cell.getErrorCellValue());
                break;

            default:
                // 默认处理
                cellValue = dataFormatter.formatCellValue(cell);
                break;

        }
        return cellValue;
    }
}
